
let mysql = require('mysql');
//准备数据库连接
//创建数据库指令 create database position CHARSET utf8; 建库时设置为utf8格式，否则数据插入时会出现错误
var Con = mysql.createConnection(
    {
        host: "localhost",
        user: "root",
        password: "root",
        database: "position"
    }
);

//链接数据库
Con.connect()

//创建表格
let createT = `create table if not exists us(
    u_id int primary key auto_increment,
    u_name VARCHAR(100),
    u_gender VARCHAR(20),
    u_age int,
    c_date datetime,
    u_date datetime
    ); `

Con.query(createT, (err, data) => {
    if (err) {
        return console.log(err)
    }
    if (!data.affectedRows) {
        return console.log("表格创建：当前已存在此表格")
    }
    console.log("表格创建成功")
})

//创建一个人
function time() {
    let today = new Date();
    let yyy = today.getFullYear();
    let mmm = today.getMonth() + 1;
    let ddd = today.getDate();
    let hh = today.getHours();
    let mm = today.getMinutes();
    let ss = today.getSeconds();
    let time = yyy + '-' + mmm + '-' + ddd + ' ' + hh + ':' + mm + ':' + ss
    return time;
}
//随机造人函数
function Sp(){
    const gender = ["'男'","'女'"]
    const names = [
    "'John'",
    "'Paul'",
    "'George'",
    "'Ringo'",
    "'Mary'",
    "'Sue'",
    "'Jack'",
    "'Jill'",
    "'Tom'",
    "'Jerry'"
    ]
    return names[Math.floor(Math.random()*names.length)]+","+gender[Math.floor(Math.random()*gender.length)]+","+Math.floor(Math.random()*60)
}
// console.log(time)
let createP = `INSERT INTO us(u_name,u_gender,u_age,c_date) VALUE (`+Sp()+`,` + `'` + time() + `'` + `)`

Con.query(
    createP,
    (err, data) => {
        if (err) {
            return console.log(err)
        }
        console.log("人物创建成功")
    }
)
//删除一个人
let u_id = 2;
let deleteP = 'DELETE FROM us where (u_id = ' + u_id + ')'
Con.query(deleteP, (err, data) => {
    if (err) {
        return console.log(err)
    }
    if (!data.affectedRows) {
        return console.log("人物删除：未找到满足条件的数据")
    }
    console.log("人物删除成功")
})

//修改一个人

let updateP = `UPDATE us set u_name = 'Guld',u_date = '` + time() + `' where u_name = 'jazzy'`
Con.query(
    updateP,
    (err, data) => {
        if (err) {
            return console.log(err)
        }
        if (!data.affectedRows) {
            return console.log("人物修改：未找到满足条件的数据")
        }
        console.log("成功修改")
    }
)

//查看当前表内有哪些人,每页显示3个
function rTime(date) {
    var json_date = new Date(date).toJSON();
    return new Date(new Date(json_date) + 8 * 3600 * 1000).toISOString().replace(/T/g, ' ').replace(/\.[\d]{3}Z/, '')
}

let selectP = `select * from us limit ?,?`
let arrP = [];
const S = 10;
const M = 4;// (想看第几页-1)*页数 = 页码
let selectS = [(M-1)*S,S]//页码，页数
Con.query(
    selectP,
    selectS,
    (err, data) => {
        if (err) {
            return console.log(err)
        }
        arrP = data;
    }
)
//男女个数和总数的统计,能统计出（小于18） 和 18-45 和 45岁以上的人数
let selectG= `SELECT 
    SUM(CASE WHEN u_age < 18 THEN 1 ELSE 0 END) AS '小于18岁',
    SUM(CASE WHEN u_age BETWEEN 18 AND 45 THEN 1 ELSE 0 END) AS '18-45岁',
    SUM(CASE WHEN u_age > 45 THEN 1 ELSE 0 END) AS '45岁以上',
    SUM(CASE WHEN u_gender = '男' THEN 1 ELSE 0 END) AS '男性',
    SUM(CASE WHEN u_gender = '女' THEN 1 ELSE 0 END) AS '女性',
    COUNT(*) AS '总数'
FROM 
    us;`
    Con.query(
        selectG,
        (err,data)=>{
            if(err) return console.log("错误:"+err.message)
            console.log("-----------------------------------统计------------------------------------")
            console.log(data[0])
        }
    )
setTimeout(() => {
    console.log("-----------------------------------第"+(M)+"页表格内容如下------------------------------------")
    arrP.forEach(p => {
        console.log(
            "id:" + p.u_id,
            "名字：" + p.u_name,
            "性别:"+p.u_gender,
            "年龄：" + p.u_age,
            "创建时间：" + rTime(p.c_date),
            "修改时间：" + rTime(p.u_date)
        )
    });
    
}, 1000);

Con.end()
